[Oracle] How to Restrict User Login with IP Using Triggers
- 2021-08-12 03:56:05
- OfStack
Here is an example of a trigger:
The trigger imposes an IP restriction on user EPAY_USER (only '192.168. 219.20', '192.168. 219.22' are allowed, and if you need to set IP segment, you can replace it with% or?, such as' 192.168. 219.% ').
Here are a few examples of Test 1:
1) Log in from a non-IP address (192.168. 219.21) and the connection failed
2) Log in from the IP address (192.168. 219.22) and the connection is successful
3) Login from local (192.168. 219.23) is not affected by IP restriction, and the connection is successful
create or replace trigger logon_ip_control
after logon on database
declare
ip STRING(30);
user STRING(30);
begin
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual;
if user='EPAY_USER'
THEN
IF ip not in ('192.168.219.20','192.168.219.22')
THEN raise_application_error(-20001,'User '||user||' is not allowed to connect from '||ip);
END IF;
END IF;
end;
/
The trigger imposes an IP restriction on user EPAY_USER (only '192.168. 219.20', '192.168. 219.22' are allowed, and if you need to set IP segment, you can replace it with% or?, such as' 192.168. 219.% ').
Here are a few examples of Test 1:
1) Log in from a non-IP address (192.168. 219.21) and the connection failed
[oracle@lxdb2 ~]$ sqlplus epay_user@pri
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 19:23:48 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: User EPAY_USER is not allowed to connect from 192.168.219.21
ORA-06512: at line 10
2) Log in from the IP address (192.168. 219.22) and the connection is successful
[oracle@lxdb1 ~]$ sqlplus epay_user
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 11:24:25 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
3) Login from local (192.168. 219.23) is not affected by IP restriction, and the connection is successful
[oracle@lxdb1 ~]$ sqlplus epay_user
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 11:24:25 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options